package com.dc.commons.procedure;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;

import com.dc.commons.procedure.pager.DcPagerResult;

/*******************************************************************************<P>
 * 文件名: DcProcedure.java <br>
 * 版本: 1.0<br>
 * 描述:<br>
 * 版权所有: <br>
 * <P>
 * 创建者: 王建栋 <br>
 * 创建日期: 2016年4月26日 上午10:06:12<br>
 * 修改者: <br>
 * 修改日期: <br>
 * 修改说明: <br>
 ******************************************************************************/
public class DcProcedure {

	private static Logger logger=LoggerFactory.getLogger(DcProcedure.class);
	
	public static enum DcDataType implements DcProcedureCallable{
		Oracle(){
			@Override
			public ResultSet executeQuery(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				callableStatement.execute();
				return (ResultSet) callableStatement.getObject(1);
			}

			@Override
			public CallableStatement prepareCall(Connection conn, String proc)
					throws SQLException {
				String sql="";
				if(proc.indexOf("?")!=-1){
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+"?,"+proc.substring(index);
				}else{
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+"?"+proc.substring(index);
				}
				logger.debug(sql);
				return conn.prepareCall(sql);
			}

			@Override
			public void registerOutCursor(CallableStatement callableStatement)
					throws SQLException {
				callableStatement.registerOutParameter(1, getCursorType());
				
			}

			@Override
			public int getCursorType() {
				return -10;//OracleTypes.CURSOR;
			}
			
			@Override
			public DcPagerResult executePagerQuery(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				callableStatement.execute();
				Long totalCount=((Number)callableStatement.getObject(2)).longValue();
				ResultSet rs= (ResultSet) callableStatement.getObject(1);
				return new DcPagerResult(rs, totalCount);
			}

			@Override
			public CallableStatement preparePagerCall(Connection conn, String proc)
					throws SQLException {
				String sql="";
				String pre="?,";
				if(proc.indexOf("?")!=-1){
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+pre+proc.substring(index);
				}else{
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+pre.substring(0,pre.length()-1)+proc.substring(index);
				}
				logger.debug(sql);
				return conn.prepareCall(sql);
			}

			@Override
			public void registerPagerOutCursor(CallableStatement callableStatement)
					throws SQLException {
				callableStatement.registerOutParameter(1, getCursorType());
				callableStatement.registerOutParameter(2, Types.NUMERIC);
				
			}
		},
		SqlServer(){
			@Override
			public ResultSet executeQuery(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				ResultSet rs= callableStatement.executeQuery();
				return rs;
			}

			@Override
			public CallableStatement prepareCall(Connection conn, String proc)
					throws SQLException {
				String sql="";
				int index=proc.indexOf("{")+1;
				sql=proc.substring(0,index)+"?="+proc.substring(index);
				
				logger.debug(sql);
				return conn.prepareCall(sql);
			}

			@Override
			public void registerOutCursor(CallableStatement callableStatement)
					throws SQLException {
				callableStatement.registerOutParameter(1, getCursorType());
			}

			@Override
			public int getCursorType() {
				return 7;//Types.REAL;
			}
			
			
			/**
			 * create or replace procedure getVdnLogPage(p_cusor  out sys_refcursor,p_totalcount in out integer,p_code   in varchar2) is

			begin
      if (p_totalcount<1) then
			   select count(*) into p_totalcount from dc_sys_menu where node_code like p_code;
      end if;
			if (p_totalcount>0)  then

			     open p_cusor for select * from dc_sys_menu where node_code like p_code;
			end if;

			end getVdnLogPage;
			 */
			@Override
			public DcPagerResult executePagerQuery(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				callableStatement.execute();
				Long count=((Number)callableStatement.getObject(2)).longValue();
				ResultSet rs=null;
				if(count!=0L){
					rs= callableStatement.executeQuery();
				}
				return new DcPagerResult(rs,count);
			}

			@Override
			public CallableStatement preparePagerCall(Connection conn, String proc)
					throws SQLException {
				String sql="";
				int index=proc.indexOf("{")+1;
				sql=proc.substring(0,index)+"?="+proc.substring(index);
				
				logger.debug(sql);
				return conn.prepareCall(sql);
			}

			@Override
			public void registerPagerOutCursor(CallableStatement callableStatement)
					throws SQLException {
				callableStatement.registerOutParameter(1, getCursorType());
				callableStatement.registerOutParameter(2, Types.NUMERIC);
			}

		},
		
		MySql(){
			@Override
			public ResultSet executeQuery(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				callableStatement.execute();
				ResultSet rs= callableStatement.executeQuery();
				return rs;
			}

			@Override
			public CallableStatement prepareCall(Connection conn, String proc)
					throws SQLException {
				String sql=proc;
				if(proc.indexOf("?")!=-1){
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+"?,"+proc.substring(index);
				}else{
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+"?"+proc.substring(index);
				}
				logger.debug(sql);
				return conn.prepareCall(sql);
			}

			@Override
			public void registerOutCursor(CallableStatement callableStatement)
					throws SQLException {
				//站位
				callableStatement.setObject(1, null);
			}

			@Override
			public int getCursorType() {
				return 7;//Types.REAL;
			}
			
			@Override
			public DcPagerResult executePagerQuery(CallableStatement callableStatement)
					throws SQLException, DataAccessException {
				
				/*
				 *  out 使用以下方式
				 * callableStatement.execute();
				Long count=callableStatement.getLong(2);
				ResultSet rs=null;
				if(count!=0L){
					rs= callableStatement.executeQuery();
				}*/
				
				
				//inout的时候使用以下方式
				ResultSet rs=null;
				rs= callableStatement.executeQuery();
				Long count=callableStatement.getLong(2);
				
				return new DcPagerResult(rs,count);
			}

			/**
			 * DELIMITER $$

				USE `changan`$$
				
				DROP PROCEDURE IF EXISTS `getVdnLogPage`$$
				
				#IN v_test VARCHAR(1) 只是一个占位符 补齐任何作用
				CREATE DEFINER=`root`@`localhost` PROCEDURE `getVdnLogPage`(IN v_test VARCHAR(1),OUT v_count INT, warning_type VARCHAR(50))
				BEGIN
				    SELECT COUNT(*) INTO v_count FROM dc_record; 
				    IF(v_count>0) THEN
				     SELECT * FROM dc_record WHERE warning_type = warning_type ;
				    END IF;
				    END$$
				
				DELIMITER ;
							 */
			@Override
			public CallableStatement preparePagerCall(Connection conn, String proc)
					throws SQLException {
				String sql=proc;
				if(proc.indexOf("?")!=-1){
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+"?,"+proc.substring(index);
				}else{
					int index=proc.indexOf("(")+1;
					sql=proc.substring(0,index)+"?"+proc.substring(index);
				}
				logger.debug(sql);
				return conn.prepareCall(sql);
			}

			@Override
			public void registerPagerOutCursor(CallableStatement callableStatement)
					throws SQLException {
				//站位
				callableStatement.setString(1, "");
				callableStatement.registerOutParameter(2, Types.INTEGER);
			}

		};

		
	}
}
